package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import DB.DatabaseTables.Table;
public class UserConnector {

	private DatabaseTables tables = DatabaseTables.getInstance();
	private Table users = tables.users;
	private static UserConnector instance = null;
	
	public static UserConnector getInstance(){
		if (instance == null){
			instance = new UserConnector();
		}
		return instance;
	}
	
	private UserConnector(){
		
	}
	
	public int signIn(String name, String password) {
		Connection con = DBConnectionPool.getInstance().getConnection();
		try {
			
			if (con == null){
				return -1;
			}
			Statement st = con.createStatement();
			String q = "SELECT " + users.columns[1]  + " FROM " + users.name + " WHERE " + users.columns[0] + "='" + name + "'";
			ResultSet rs = st.executeQuery(q);
			if (rs.next()){
				if (rs.getString(1).equals(password)){
					return 0;
				}
			}
			return 1;
		}
		catch (Exception e){
			System.out.println(e.toString());
			return -1;
		}
		finally{
			 DBConnectionPool.getInstance().returnConnection(con);
		}
	}
	
	public int register(String name, String password) {
		Connection con = DBConnectionPool.getInstance().getConnection();
		try {
			if (con == null){
				return -1;
			}
			Statement st = con.createStatement();
			//check if user already in the game
			
			//check if user exists
			String q = "SELECT " + users.columns[1]  + " FROM " + users.name + " WHERE " + users.columns[0] + "='" + name + "'";
			ResultSet rs = st.executeQuery(q);
			if (rs.next()){
				if (rs.getString(1).equals(password)){
					//means user already exists in db
					return 1;
				}
			}
			//user not in db
			q = "INSERT INTO " + users.name + " VALUES ( '" + name + "','" + password + "',0,0)";
			st.executeUpdate(q);
			return 0;
		}
		catch (Exception e){
			System.out.println(e.toString());
			return -1;
		}
		finally{
			 DBConnectionPool.getInstance().returnConnection(con);
		}
	}
	
	public int updateUserScores(String name, int score) {
		Connection con = DBConnectionPool.getInstance().getConnection();
		try {
			if (con == null){
				return -1;
			}
			Statement st = con.createStatement();
			//check if user already in the game
			
			//check if user exists
			String q = "SELECT " + users.columns[2] + "," + users.columns[3];
			q += " FROM " + users.name + " WHERE " +users.columns[0] + "='" + name + "'";
			ResultSet rs = st.executeQuery(q);
			int topscore;
			int totalScore;
			if (rs.next()){
				topscore = rs.getInt(1);//the top score column
				totalScore = rs.getInt(2);//the total score column
				if (score > topscore){
					topscore = score;
				}
				totalScore += score;
				//update user's scores
				q = "UPDATE "+ users.name + " SET " + users.columns[2] + "=" +
					topscore + ", " + users.columns[3] + "=" + totalScore + 
					" WHERE " + users.columns[0] + "='" + name+"'";
				st.executeUpdate(q);
				return 0;
			}
			return -1 ;
		}
		catch (Exception e){
			System.out.println(e.toString());
			return -1;
		}
		finally{
			 DBConnectionPool.getInstance().returnConnection(con);
		}
	}
	
	public topScorer[] getTopPlayers(int numOfPlayers) {
		Connection con = DBConnectionPool.getInstance().getConnection();
		topScorer[] res = new topScorer[numOfPlayers];
		try {
			if (con == null){
				return null;
			}
			Statement st = con.createStatement();
			//check if user already in the game
			//check if user exists
			String q = "SELECT " + users.columns[0] + " ," + users.columns[2] + 
						" FROM " + users.name + " ORDER BY " + users.columns[2] + " DESC";
			ResultSet rs = st.executeQuery(q);
			int i = 0;
			while (rs.next() && i < numOfPlayers){
				//update user's scores
				res[i++] = new topScorer(rs.getString(1), rs.getInt(2));
			}
		}
		catch (Exception e){
			System.out.println(e.toString());
		}
		finally{
			 DBConnectionPool.getInstance().returnConnection(con);
		}
		return res;
	}
	
	public topScorer[] getTopScorers(int numOfPlayers) {
		Connection con = DBConnectionPool.getInstance().getConnection();
		topScorer[] res = new topScorer[numOfPlayers];
		try {
			if (con == null){
				return null;
			}
			Statement st = con.createStatement();
			//check if user already in the game
			//check if user exists
			String q = "SELECT " + users.columns[0] + " ," + users.columns[3] + 
			" FROM " + users.name + " ORDER BY " + users.columns[3] + " DESC";
			ResultSet rs = st.executeQuery(q);
			int i =0;
			while (rs.next()&& i < numOfPlayers){
				//update user's scores
				res[i++] = new topScorer(rs.getString(1),rs.getInt(2));
			}
		}
		catch (Exception e){
			System.out.println(e.toString());
		}
		finally{
			 DBConnectionPool.getInstance().returnConnection(con);
		}
		return res;
	}
}